# -*- coding: utf-8 -*-
# @Time    : 2024/11/6 上午1:03
# @Author  : xiaowanzi
# @File    : mysql_pool.py

import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from contextlib import contextmanager


class MySQLPool:
    def __init__(self, user, password, host, database, port=3306, pool_name="pool_base", pool_size=5):
        self.pool = MySQLConnectionPool(
            pool_name=pool_name,
            pool_size=pool_size,
            user=user,
            password=password,
            host=host,
            database=database,
            port=port
        )

    @contextmanager
    def get_connection(self):
        conn = self.pool.get_connection()
        try:
            yield conn
        finally:
            conn.close()

    def execute_sql(self, sql, params=None):
        with self.get_connection() as conn:
            cursor = conn.cursor(dictionary=True)
            try:
                cursor.execute(sql, params)
                if sql.strip().upper().startswith("SELECT"):
                    result = cursor.fetchall()
                    return result
                else:
                    conn.commit()
            except mysql.connector.Error as err:
                print(f"Error: {err}")
                conn.rollback()
            finally:
                cursor.close()


if __name__ == "__main__":
    # 替换为实际的数据库连接信息
    db_config = {
        'user': 'root',
        'password': 'mysql19930920?',
        'host': '127.0.0.1',
        'database': 'base'
    }

    pool = MySQLPool(**db_config)

    # 执行查询
    sql_query = "SELECT * FROM user WHERE username = %s"
    params = ('liaohf',)
    result = pool.execute_sql(sql_query, params)
    print(result)

    # 执行更新
    sql_update = "UPDATE user SET full_name = %s WHERE username = %s"
    update_params = ('liao', 'liaohf')
    pool.execute_sql(sql_update, update_params)
